﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data.SqlClient;

namespace PhoneNumberOrder
{
    public partial class OrderUserManageFrm : Form
    {
        public OrderUserManageFrm()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if(openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                List<string[]> userList = Global.userList;
                if(userList.Count > 0 && MessageBox.Show("是否清空旧的预约帐号？","确认", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    userList = new List<string[]>();

                //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0";
                //OleDbConnection con = new OleDbConnection(strCon);
                //try
                //{
                //    con.Open();
                //    OleDbCommand cmd = new OleDbCommand("SELECT   *   FROM   [Sheet1$]", con);
                //    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                //    DataSet ds = new DataSet();
                //    da.Fill(ds, "[Sheet1$]");

                //    for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                //    {
                //        DataRow row = ds.Tables[0].Rows[i];
                //        string strUserName = row.IsNull("客户姓名") ? "" : row["客户姓名"].ToString();
                //        //string strCardType = row.IsNull("证件类型") ? "身份证" : row["证件类型"].ToString();
                //        string strCardNum = row.IsNull("证件号码") ? "" : row["证件号码"].ToString();
                //        string strPhone = row.IsNull("联系电话") ? "" : row["联系电话"].ToString();

                //        userList.Add(new string[] { strUserName, strCardNum, strPhone, "0" });
                //    }
                //}
                //catch(Exception ex)
                //{
                //    MessageBox.Show("导入预约帐号出错：" + ex.Message);
                //    return;
                //}
                //finally
                //{
                //    con.Close();
                //}


                Stream ms = null;
                try
                {
                    ms = new FileStream(openFileDialog1.FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None);
                    HSSFWorkbook workbook = new HSSFWorkbook(ms);
                    ISheet sheet = workbook.GetSheetAt(0);
                    IRow headerRow = sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    DataTable table = new DataTable();
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    int rowCount = sheet.LastRowNum;
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum ; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        table.Rows.Add(dataRow);
                    }

                    foreach (DataRow row in table.Rows)
                    {
                        string strUserName = row.IsNull("客户姓名") ? "" : row["客户姓名"].ToString();
                        //string strCardType = row.IsNull("证件类型") ? "身份证" : row["证件类型"].ToString();
                        string strCardNum = row.IsNull("证件号码") ? "" : row["证件号码"].ToString();
                        string strPhone = row.IsNull("联系电话") ? "" : row["联系电话"].ToString();

                        userList.Add(new string[] { strUserName, strCardNum, strPhone, "0" });
                    }
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("导入预约帐号出错：" + ex.Message);
                    return;
                }
                finally
                {
                    if(ms != null)
                        ms.Close();
                }

                SerializeHelper.Serialize(Global.userListFilePath, userList);
                Global.userList = userList;
                refreshGrid();
                MessageBox.Show("导入成功！");
            }
        }

        private void OrderUserManageFrm_Load(object sender, EventArgs e)
        {
            refreshGrid();
        }

        private void refreshGrid()
        {
            this.dataGridView1.Rows.Clear();

            DataGridViewRow row;
            foreach (string[] item in Global.userList)
            {
                this.dataGridView1.Rows.Add();

                row = this.dataGridView1.Rows[this.dataGridView1.RowCount - 1];
                for (int i = 0; i < item.Length; i++)
                {
                    row.Cells[i].Value = item[i];
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                Stream ms = null;
                try
                {
                    ms = new FileStream(openFileDialog1.FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None);
                    HSSFWorkbook workbook = new HSSFWorkbook(ms);
                    ISheet sheet = workbook.GetSheetAt(0);
                    IRow headerRow = sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    DataTable table = new DataTable();
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    int rowCount = sheet.LastRowNum;
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        table.Rows.Add(dataRow);
                    }
                    string strConn = "Data Source=172.16.8.97;Initial Catalog=JXPJ;User ID=sa;Password=sa;";
                    MySqlHelper helper = new MySqlHelper(strConn);
                    helper.BeginTransaction();
                    try
                    {
                        string strSQL = "delete from [user] where [deptid] ='" +this.textBox1.Text+ "'";
                        helper.ExecuteNonQuery(strSQL, null);
                        foreach (DataRow row in table.Rows)
                        {
                            string strUserName = row.IsNull("姓名") ? "" : row["姓名"].ToString();
                            strSQL = string.Format("insert into [user] ([guid],[name],[deptid],[ordernum]) values ('{0}','{1}','{2}',{3})"
                            , Guid.NewGuid().ToString(), strUserName, this.textBox1.Text, this.textBox2.Text);
                            helper.ExecuteNonQuery(strSQL, null);

                        }

                        helper.EndTransaction(true);
                    }
                    catch (System.Exception ex)
                    {
                    	helper.EndTransaction(false);
                        throw ex;
                    }
                    
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("导入出错：" + ex.Message);
                    return;
                }
                finally
                {
                    if (ms != null)
                        ms.Close();
                }

                MessageBox.Show("导入成功！");
            }
        }
    }
}